{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efbd5c1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#imports\n",
    "from dotenv import load_dotenv\n",
    "from openai import OpenAI\n",
    "import json\n",
    "import os, time\n",
    "import requests\n",
    "from pypdf import PdfReader\n",
    "import gradio as gr\n",
    "import pyodbc\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a13791fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# The usual start\n",
    "load_dotenv(override=True)\n",
    "openai = OpenAI()\n",
    "ai_model=\"gpt-4o-mini\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49468af2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Access Database and Table Details\n",
    "DB_PATH = r\"C:\\Users\\giris\\AgenticAIProjects\\agents\\MyCode\\Optima\\OptimaTracker.accdb\"\n",
    "UserDetailsTable = \"InterestedUser\"\n",
    "UnknownQuestionTable = \"UnknownQuestion\"\n",
    "AnswerTable = \"QuestionsAnswered\"\n",
    "LastRowCount = None\n",
    "NewInformation = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec409fee",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Connection String\n",
    "def open_db (db_path=DB_PATH):\n",
    "    conn_str = (\n",
    "        r\"Driver={Microsoft Access Driver (*.mdb, *.accdb)};\"\n",
    "        rf\"DBQ={db_path};\"\n",
    "    )\n",
    "\n",
    "    #connect to DB\n",
    "    dbconn = pyodbc.connect(conn_str,autocommit=False)\n",
    "    dbcursor = dbconn.cursor()\n",
    "    return dbconn, dbcursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4efb7fb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def close_db(dbconn, dbcursor, commit=True):\n",
    "    try:\n",
    "        if commit:\n",
    "            dbconn.commit()\n",
    "        else:\n",
    "            dbconn.rollback()\n",
    "    finally:\n",
    "        dbcursor.close()\n",
    "        dbconn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efc8384c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def commit_db(dbconn):\n",
    "    try:\n",
    "        dbconn.commit()\n",
    "        return(True)\n",
    "    except Exception as e:\n",
    "        print(\"Error\", e)\n",
    "        return(False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "baf9555d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_questions_answered ():\n",
    "    MoreInformation = \"\"\n",
    "    conn, cur = open_db()\n",
    "    AnswerTableSql = \"Select QuestionAsked, Answers from \" + AnswerTable\n",
    "    cur.execute(AnswerTableSql)\n",
    "    tbrows = cur.fetchall()\n",
    "    for row in tbrows:\n",
    "        MoreInformation += \"Question: \" + row[0] + \"\\nAnswer: \" + row[1] + \"\\n\"\n",
    "    close_db(conn, cur, True)\n",
    "    #print(MoreInformation)\n",
    "    return (MoreInformation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a49d6420",
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_table_update ():\n",
    "    conn, cur = open_db()\n",
    "    AnswerTableSql = \"Select count(*), MAX(Id) from \" + AnswerTable\n",
    "    cur.execute(AnswerTableSql)\n",
    "    cnt, max_id = cur.fetchone()\n",
    "    return (cnt or 0, max_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ad9e0cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "def record_unknown_question(question):\n",
    "    conn, cur = open_db()\n",
    "    UnknowQuestionInsSql = f\"INSERT INTO {UnknownQuestionTable}(UserQuestion) VALUES (?)\"\n",
    "    cur.execute(UnknowQuestionInsSql, (question,))\n",
    "    if commit_db(conn):\n",
    "        close_db(conn, cur, True)\n",
    "        return {\"recorded\": \"ok\"}\n",
    "    else:\n",
    "        close_db(conn, cur, True)\n",
    "        return {\"recorded\": \"Notok\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3b2280a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def record_user_details(email, name=\"NotProvided\", notes=\"NotProvided\"):\n",
    "    conn, cur = open_db()\n",
    "    UserInsertSql = f\"INSERT INTO {UserDetailsTable}(username, usermail, Notes) VALUES (?,?,?)\"\n",
    "    cur.execute(UserInsertSql, (name, email, notes))\n",
    "    if commit_db(conn):\n",
    "        close_db(conn, cur, True)\n",
    "        return {\"recorded\": \"ok\"}\n",
    "    else:\n",
    "        close_db(conn, cur, True)\n",
    "        return {\"recorded\": \"Notok\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68f80db2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Information = check_questions_answered()\n",
    "#print(Information)\n",
    "#Question=\"Who is your daughter\"\n",
    "#UserName = \"Girish\"\n",
    "#UserEmail = \"girish@girish.com\"\n",
    "#UserNotes = \"Pls connect with me\"\n",
    "#answer = record_unknown_question(Question)\n",
    "#print(\"Commited: \", Question, answer)\n",
    "#answer2= record_user_details (UserName, UserEmail, UserNotes)\n",
    "#print (\"Commited\", UserName, answer2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f214f37e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the response json structure that the LLM will send back for Fuction # 1\n",
    "record_user_details_json = {\n",
    "    \"name\": \"record_user_details\",\n",
    "    \"description\": \"Use this tool to record that a user is interested in being in touch and provided an email address\",\n",
    "    \"parameters\": {\n",
    "        \"type\": \"object\",\n",
    "        \"properties\": {\n",
    "            \"email\": {\n",
    "                \"type\": \"string\",\n",
    "                \"description\": \"The email address of this user\"\n",
    "            },\n",
    "            \"name\": {\n",
    "                \"type\": \"string\",\n",
    "                \"description\": \"The user's name, if they provided it\"\n",
    "            }\n",
    "            ,\n",
    "            \"notes\": {\n",
    "                \"type\": \"string\",\n",
    "                \"description\": \"Any additional information about the conversation that's worth recording to give context\"\n",
    "            }\n",
    "        },\n",
    "        \"required\": [\"email\"],\n",
    "        \"additionalProperties\": False\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9c3e3d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the response json structure that the LLM will send back for Fuction # 2\n",
    "record_unknown_question_json = {\n",
    "    \"name\": \"record_unknown_question\",\n",
    "    \"description\": \"Always use this tool to record any question that couldn't be answered as you didn't know the answer\",\n",
    "    \"parameters\": {\n",
    "        \"type\": \"object\",\n",
    "        \"properties\": {\n",
    "            \"question\": {\n",
    "                \"type\": \"string\",\n",
    "                \"description\": \"The question that couldn't be answered\"\n",
    "            },\n",
    "        },\n",
    "        \"required\": [\"question\"],\n",
    "        \"additionalProperties\": False\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7bc36ad7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Now Define the tools / functions that the LLM has options for a response\n",
    "tools = [{\"type\": \"function\", \"function\": record_user_details_json},\n",
    "        {\"type\": \"function\", \"function\": record_unknown_question_json}]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eba93f09",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define how to handle the response back from LLM based on what tool/function the LLM asked us to use\n",
    "def handle_tool_calls(tool_calls):\n",
    "    results = []\n",
    "    for tool_call in tool_calls:\n",
    "        tool_name = tool_call.function.name\n",
    "        arguments = json.loads(tool_call.function.arguments)\n",
    "        tool = globals().get(tool_name)\n",
    "        #print(\"Tool called\", tool, \"Arguments\", arguments)\n",
    "        result = tool(**arguments) if tool else {}\n",
    "        results.append({\"role\": \"tool\",\"content\": json.dumps(result),\"tool_call_id\": tool_call.id})\n",
    "        \n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8806ba08",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now load Optima's Business Description from the pdf\n",
    "reader = PdfReader(\"Optima/OptimaBusinessDescription.pdf\")\n",
    "OptimaBusinessDescription = \"\"\n",
    "for page in reader.pages:\n",
    "    text = page.extract_text()\n",
    "    if text:\n",
    "        OptimaBusinessDescription += text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91649d7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now Load the Summary provided by Optima in the text file\n",
    "with open(\"Optima/OptimaSummary.txt\", \"r\", encoding=\"utf-8\") as f:\n",
    "    OptimaSummary = f.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17653e56",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Set Company Name to add to context for Agent\n",
    "CompanyName = \"Optima Business Solutions LLC\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9061dce2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Build the System Prompt to set context to Agent to ask the LLM\n",
    "system_prompt = f\"You are acting as a spokeman for {CompanyName}. You are answering questions on {CompanyName}'s website, \\\n",
    "particularly questions related to {CompanyName}'s offerings, background, skills and experience. \\\n",
    "Your responsibility is to represent {CompanyName} for interactions on the website as faithfully as possible. \\\n",
    "You are given a summary of {CompanyName}'s background and Business profile which you can use to answer questions. \\\n",
    "Be professional and engaging, as if talking to a potential client or future employees who came across the website. \\\n",
    "If you don't know the answer to any question, use your record_unknown_question tool to record the question that you \\\n",
    "couldn't answer, even if it's about something trivial or unrelated to career. \\\n",
    "If the user is engaging in discussion, try to steer them towards getting in touch via email; \\\n",
    "ask for their email, name and short message and record it using your record_user_details tool. \"\n",
    "\n",
    "system_prompt += f\"\\n\\n## Summary:\\n{OptimaSummary}\\n\\n## Business Profile:\\n{OptimaBusinessDescription}\\n\\n\"\n",
    "system_prompt += f\"With this context, please chat with the user, always staying in character as {CompanyName}.\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14a2d01f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now we build the actual chat function.\n",
    "def chat(user_message, history):\n",
    "    global LastRowCount\n",
    "    global NewInformation\n",
    "    count, maxid = check_table_update()\n",
    "    if (LastRowCount is None):\n",
    "        LastRowCount = count\n",
    "        NewInformation = check_questions_answered()  \n",
    "        #print(\"New Lookup got first rows\")\n",
    "    elif count != LastRowCount:\n",
    "        LastRowCount = count\n",
    "        NewInformation = check_questions_answered()  \n",
    "        #print(\"New Lookup got new rows\")\n",
    "    #else:\n",
    "        #print(\"No new lookup\")\n",
    "\n",
    "    helper_prompt = [{\"role\": \"system\", \"content\" : NewInformation}]\n",
    "    #system_prompt += f\"\\n\\n##Use this additional informaton \\n\\n {NewInformation}, \\n always staying in character as {CompanyName} when chatting with the user.\"\n",
    "    messages = [{\"role\": \"system\", \"content\": system_prompt}] + helper_prompt+ history + [{\"role\": \"user\", \"content\": user_message}]\n",
    "    # The following while loop will determine if LLM has responded with a tool call or a user response\n",
    "    #print(messages)\n",
    "    ResponseforUser = False\n",
    "    while not ResponseforUser:\n",
    "\n",
    "        # This is the call to the LLM - see that we pass in the tools json\n",
    "\n",
    "        response = openai.chat.completions.create(model=ai_model, messages=messages, tools=tools)\n",
    "     \n",
    "        # The finish_reason will have the LLM response end status i.e. it the call finished with a tool call or something else. We interpret\n",
    "        # the something else as a user response\n",
    "        finish_reason = response.choices[0].finish_reason\n",
    "           \n",
    "        # If the LLM wants to call a tool, we do that!\n",
    "         \n",
    "        if finish_reason==\"tool_calls\":\n",
    "            message = response.choices[0].message\n",
    "            tool_calls = message.tool_calls\n",
    "            results = handle_tool_calls(tool_calls)\n",
    "            messages.append(message)\n",
    "            messages.extend(results)\n",
    "        else:\n",
    "            ResponseforUser = True\n",
    "    return response.choices[0].message.content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c37ae6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now we create the chat interface\n",
    "gr.ChatInterface(chat, type=\"messages\").launch()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "465fe770",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
